-- et ... event type, rt ... run type, pt ... priority type (0 ... basic, 1 ... legislative, 2 ... licence)
<g>
create type SourceEventRecords as table (ic bigint, u int, ic_a bigint, u_a int, et int, rt int, pt int);
</g>

<g>
create type EventLicences as table (u int);
</g>

<g>
create function GetEventResults (@SourceRecords SourceEventRecords READONLY, @InvertLicences bit, @Licences EventLicences READONLY) returns bit as
begin  
  -- events which replace other events in QI1
  -- must be revieved
  return case when 
    -- events which are not run in QI1 and will be run in QI2 (in QI1 not run before and after events with equal or lower priority than normal event)
    exists(select distinct a.* from @SourceRecords as a
      join @SourceRecords as b on a.ic_a = b.ic_a and a.u_a = b.u_a and a.et = b.et and a.rt = b.rt and a.pt > b.pt -- find event with higher priority (same et and rt)
      left outer join @Licences as c on a.u = c.u -- licences filter
    where (@InvertLicences = 0 and c.u is not null) or (@InvertLicences = 1 and c.u is null))
  or
    -- must be repaired (integrity error)
    exists(select distinct a.* from @SourceRecords as a
      left outer join @SourceRecords as b on a.ic_a = b.ic_a and a.u_a = b.u_a and a.et = b.et and a.rt = b.rt and a.pt < b.pt -- ensure that before and after events with not highest priority are fitered out
      join @SourceRecords as c on a.ic_a = c.ic_a and a.u_a = c.u_a and a.et = c.et and c.rt = 0 and a.pt <= c.pt -- find normal event with equal or higher priority
      left outer join @SourceRecords as d on c.ic_a = d.ic_a and c.u_a = d.u_a and c.et = d.et and c.rt = d.rt and c.pt < d.pt -- ensure that normal events with not highest priority are fitered out
      left outer join @Licences as e on a.u = e.u -- licences filter
    where a.rt <> 0 and b.ic is null and b.u is null and d.ic is null and d.u is null and ((@InvertLicences = 0 and e.u is not null) or (@InvertLicences = 1 and e.u is null)))
  then 1 else 0 end
end
</g>

<g>
create function dbo.GetEventConflictsCount() returns bit as
begin
  declare @conflicts bit -- 0 .. no conflicts ; 1 .. any conflicts
  set @conflicts = 0
  declare @node int
  declare @legic bigint
  declare @legu int
  select @node = a15832u10 from a15830u10
   -- select @node = 10100 -- Node example (if not used database node will be used)
  select @legic = ic_b, @legu = u_b from a745990u10 -- 745998,10 czech ; 740878,10 slovak
   -- select @legic = 740878, @legu = 10 -- Legislative example (if not used database legislative will be used)

  declare @EventRecords SourceEventRecords
  declare @Licences EventLicences;
  declare @InvertLicences bit;
  insert into @Licences(u) values (10),(10015),(10098),(10100),(10196),(10304); -- Licence filter for central develop which will be repaired in this reinstall (if not used all licences will be used)
  select @InvertLicences = 1

  -- dataset events
  insert into @EventRecords (ic,u,ic_a,u_a,et,rt,pt)
    select ic,u,ic_a,u_a,a247u1 as et,isnull(a745874u10,0) as rt,case when a740866u10 is null and a740864u10 is null and a745475u10 is null then 0 when a740866u10 is null then 1 else 2 end as pt from a141u0 where (a740866u10 is null or a740866u10 = cast(@node as nvarchar)) and ((a740864u10 is null and a745475u10 is null) or (a740864u10 = @legic and a745475u10 = @legu))
  select @conflicts = @conflicts | dbo.GetEventResults(@EventRecords, @InvertLicences, @Licences);
  delete from @EventRecords

  -- internal field events
  insert into @EventRecords (ic,u,ic_a,u_a,et,rt,pt)
    select ic,u,ic_a,u_a,a248u1 as et,isnull(a745888u10,0) as rt,case when a745882u10 is null and a745884u10 is null and a745886u10 is null then 0 when a745882u10 is null then 1 else 2 end as pt from a142u0 where (a745882u10 is null or a745882u10 = cast(@node as nvarchar)) and ((a745884u10 is null and a745886u10 is null) or (a745884u10 = @legic and a745886u10 = @legu))
  select @conflicts = @conflicts | dbo.GetEventResults(@EventRecords, @InvertLicences, @Licences);
  delete from @EventRecords

  -- form events
  insert into @EventRecords (ic,u,ic_a,u_a,et,rt,pt)
    select ic,u,ic_a,u_a,a255u0 as et,isnull(a745918u10,0) as rt,case when a745892u10 is null and a745898u10 is null and a745910u10 is null then 0 when a745892u10 is null then 1 else 2 end as pt from a145u0 where (a745892u10 is null or a745892u10 = cast(@node as nvarchar)) and ((a745898u10 is null and a745910u10 is null) or (a745898u10 = @legic and a745910u10 = @legu))
  select @conflicts = @conflicts | dbo.GetEventResults(@EventRecords, @InvertLicences, @Licences);
  delete from @EventRecords

  -- memory field events
  insert into @EventRecords (ic,u,ic_a,u_a,et,rt,pt)
    select ic,u,ic_a,u_a,a262u0 as et,isnull(a745916u10,0) as rt,case when a745890u10 is null and a745902u10 is null and a745906u10 is null then 0 when a745890u10 is null then 1 else 2 end as pt from a164u0 where (a745890u10 is null or a745890u10 = cast(@node as nvarchar)) and ((a745902u10 is null and a745906u10 is null) or (a745902u10 = @legic and a745906u10 = @legu))
  select @conflicts = @conflicts | dbo.GetEventResults(@EventRecords, @InvertLicences, @Licences);
  delete from @EventRecords

  -- internal field F4 events
  insert into @EventRecords (ic,u,ic_a,u_a,et,rt,pt)
    select ic,u,ic_a,u_a,a2254u0 as et,isnull(a745914u10,0) as rt,case when a745894u10 is null and a745904u10 is null and a745908u10 is null then 0 when a745894u10 is null then 1 else 2 end as pt from a195u0 where (a745894u10 is null or a745894u10 = cast(@node as nvarchar)) and ((a745904u10 is null and a745908u10 is null) or (a745904u10 = @legic and a745908u10 = @legu))
  select @conflicts = @conflicts | dbo.GetEventResults(@EventRecords, @InvertLicences, @Licences);
  delete from @EventRecords

  -- memory field F4 events
  insert into @EventRecords (ic,u,ic_a,u_a,et,rt,pt)
    select ic,u,ic_a,u_a,a2428u0 as et,isnull(a2427u0,0) as rt,case when a2424u0 is null and a2425u0 is null and a2426u0 is null then 0 when a2424u0 is null then 1 else 2 end as pt from a2195u0 where (a2424u0 is null or a2424u0 = cast(@node as nvarchar)) and ((a2425u0 is null and a2426u0 is null) or (a2425u0 = @legic and a2426u0 = @legu))
  select @conflicts = @conflicts | dbo.GetEventResults(@EventRecords, @InvertLicences, @Licences);
  delete from @EventRecords

  -- print events
  insert into @EventRecords (ic,u,ic_a,u_a,et,rt,pt)
    select ic,u,ic_a,u_a,a274926u10 as et,isnull(a745920u10,0) as rt,case when a745896u10 is null and a745900u10 is null and a745912u10 is null then 0 when a745896u10 is null then 1 else 2 end as pt from a274890u10 where (a745896u10 is null or a745896u10 = cast(@node as nvarchar)) and ((a745900u10 is null and a745912u10 is null) or (a745900u10 = @legic and a745912u10 = @legu))
  select @conflicts = @conflicts | dbo.GetEventResults(@EventRecords, @InvertLicences, @Licences);
  delete from @EventRecords

  return @conflicts
end
</g>